package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by pjn on 2017/8/11.
 */
public class CoverFaultRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public Integer getCoverReportFaultCount4DT(Integer actorId, Integer userId, Integer orgId, String strSearch) throws Exception {
        String sql = "select count(*) " +
                "FROM (((tb_cover_fault cf JOIN tb_user u ON u.UserId=cf.CoverFaultCreatorId)\n" +
                "JOIN tb_org o ON o.OrgId=cf.CoverOrgId)JOIN tb_cover c ON c.CoverId=cf.CoverId)\n" +
                "WHERE concat(c.CoverStartRoad,c.CoverEndRoad,c.CoverRoadName)like '%" + strSearch + "%'";
        if (actorId == 3) {
            sql += " and cf.CoverOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and cf.CoverFaultCreatorId=" + userId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTCoverFaultDto> getCoverReportFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "SELECT cf.*,u.UserName AS coverFaultCreatorName,u2.UserName AS coverMaintainerName,u3.UserName " +
                "AS coverMainCheckerName, o.OrgName AS coverOrgName,c.CoverPhoto from tb_cover_fault cf left join tb_user u on" +
                " u.UserId=cf.CoverFaultCreatorId left join tb_user u2 on u2.userId = cf.CoverMaintainerId " +
                "left join tb_user u3 on u3.userId=cf.CoverMainCheckerId left join tb_org o on o.orgId=cf.CoverOrgId " +
                "left join tb_cover c on c.CoverId=cf.CoverId " +
                "WHERE concat(c.CoverRoadName,c.CoverStartRoad,c.CoverEndRoad)like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3) {
            sql += " and cf.CoverOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and cf.CoverFaultCreatorId=" + userId + "";
        }
        sql += " AND cf.CoverCheck IN (0,1,-1,2) \n" +
                " order by find_in_set( cf.CoverCheck ,'0,2,1,-1') , cf.CoverFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTCoverFaultDto.class));
        List<DTCoverFaultDto> coverFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return coverFaultDtos;
    }

    public List<DTCoverFaultDto> getCoverFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT mf.CoverFaultId,mf.CoverId,CoverFaultPhoto,mf.CoverFaultCreatorId,CoverFaultTime," +
                "CoverCheckerId,CoverMoney,CoverFaultLevel,CoverFaultType,mf.CoverFaultReason,\n" +
                "CoverMainCheckerId,CoverCheck,CoverMainCheck,mf.CoverOrgId,mf.CoverFeedback,mf.CoverMainOrgId,OrgName AS coverOrgName,CoverPhoto \n" +
                "FROM tb_cover_fault mf,tb_cover sm,tb_org o\n" +
                "WHERE mf.CoverOrgId=o.OrgId AND mf.CoverId = sm.CoverId " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.CoverCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " AND mf.CoverMainOrgId=" + orgId + "";
        }
        sql += "  AND  mf.CoverMainCheck IN (0,1,-1) \n" +
                " order by  find_in_set( mf.CoverMainCheck ,'0,1,-1') , mf.CoverFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTCoverFaultDto.class));
        List<DTCoverFaultDto> machineFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return machineFaultDtos;
    }

    public Integer getCoverFaultCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_cover_fault mf,tb_cover sm,tb_org o\n" +
                "WHERE mf.CoverOrgId=o.OrgId AND mf.CoverId = sm.CoverId " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.CoverMainCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " AND mf.CoverMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTCoverFaultDto> getCoverFaultHandle4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer machineMaintenanceStatus) throws Exception {
        String sql = "SELECT u.userName AS coverFaultCreatorName, CoverFaultId,OrgName AS coverOrgName,CoverFaultCreatorId,CoverFaultTime,CoverCheckerId,CoverFaultType,CoverFaultLevel,CoverMaintenancePhoto,CoverMaintainerId," +
                "CoverFaultReason,CoverMaintenanceStatus,CoverMainCheckTime,CoverMaintenanceExplain,CoverLongitude,CoverLatitude," +
                "CoverMainCheckerId,mf.CoverId,CoverMaintenanceContent,CoverFeedback,CoverStartRoad,CoverEndRoad,CoverRoadName," +
                "CoverFaultPhoto,CoverPhoto,CoverArea,CoverCity,CoverProvince,mf.CoverOrgId,mf.coverMoney,mf.coverMaintenanceTime\n" +
                "FROM tb_cover sm,tb_cover_fault mf,tb_org o,tb_user u\n" +
                "WHERE mf.CoverId=sm.CoverId AND mf.CoverOrgId=o.OrgId AND u.userId=mf.coverFaultCreatorId AND mf.CoverMainCheck in (1,2) " +
                "AND concat(sm.CoverStartRoad, sm.CoverEndRoad,sm.CoverRoadName) like '%" + dtRequestDto.getSearch() + "%' AND mf.CoverCheck in (1,2) ";
        //根据维修人员和系统管理员区分
        if (machineMaintenanceStatus != 3) {
            sql += " and mf.CoverMaintenanceStatus = " + machineMaintenanceStatus + " ";
        }

        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND mf.CoverOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND mf.CoverMainOrgId =" + orgId + "";
        }
        sql += " order by mf.CoverMaintenanceStatus ASC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTCoverFaultDto.class));
        List<DTCoverFaultDto> solarFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return solarFaultDtos;
    }

    public Integer getCoverFaultHandleCount4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer machineMaintenanceStatus) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_signal_machine sm,tb_machine_fault mf,tb_org o\n" +
                "WHERE mf.SignalMachineId=sm.SignalMachineId AND mf.MachineOrgId=o.OrgId AND mf.MachineMainCheck=1 " +
                "AND concat(sm.SignalMachineStartRoad, sm.SignalMachineEndRoad) like '%" + dtRequestDto.getSearch() + "%' AND mf.MachineCheck=1 ";
        //根据维修人员和系统管理员区分
        if (machineMaintenanceStatus != 3) {
            sql += " and mf.machineMaintenanceStatus = " + machineMaintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND mf.MachineOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND mf.MachineMainOrgId =" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<CoverFaultDto> getCoverFaultCheckList4App(Integer orgId, String actorIds, Integer faultStatus,
                                                          Integer count, Integer size) throws Exception {
        String sql = " SELECT mf.CoverFaultId,mf.CoverId,mf.CoverFaultPhoto,mf.CoverFaultType,mf.CoverFaultLevel," +
                "mf.CoverFaultReason,mf.CoverFaultTime,mf.CoverCheck,mf.CoverMainCheck,mf.CoverMainCheckTime " +
                "FROM tb_cover_fault mf ";
        if (actorIds.indexOf("1") >= 0) {//系统管理员
            if (faultStatus == 0) {//待审核
                sql += " WHERE (mf.CoverCheck =0) OR (mf.CoverMainCheck =0 AND mf.CoverCheck=1 )  ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE  mf.CoverMainCheck =1 AND mf.CoverCheck=1  ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE ( mf.CoverCheck =-1) OR (mf.CoverMainCheck =-1 AND mf.CoverCheck=1 ) ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("3") >= 0) {//审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE mf.CoverCheck=0 AND mf.CoverOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE mf.CoverCheck=1 AND mf.CoverOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE mf.CoverCheck=-1 AND mf.CoverOrgId=" + orgId + " ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("7") >= 0) {//主管审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE mf.CoverCheck=1 AND mf.CoverMainCheck=0 AND mf.CoverMainOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE mf.CoverCheck=1 AND mf.CoverMainCheck=1 AND mf.CoverMainOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE mf.CoverCheck=1 AND mf.CoverMainCheck=-1 AND mf.CoverMainOrgId=" + orgId + " ";
            } else {
                return null;
            }
        }
        sql += " ORDER BY mf.CoverFaultTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(CoverFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }


    public List<CoverFaultDto> getCoverFaultHandleList4App(Integer orgId, Integer maintenanceStatus, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT mf.CoverFaultId,mf.CoverId,mf.CoverFaultPhoto,mf.CoverFaultType,mf.CoverFaultLevel," +
                "mf.CoverFaultReason,mf.CoverMaintenanceStatus,mf.CoverMainCheckTime,mf.CoverMaintenancePhoto," +
                "mf.CoverMaintenanceTime\n" +
                "FROM tb_cover_fault mf " +
                "WHERE mf.CoverMaintenanceStatus=" + maintenanceStatus + "";
        if (actorIds.indexOf("7") >= 0) {
            sql += " AND mf.CoverMainOrgId=" + orgId + "";
        } else if (actorIds.indexOf("5") >= 0) {
            sql += " AND mf.CoverOrgId=" + orgId + "";
        }
        sql += " ORDER BY mf.CoverMainCheckTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(CoverFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<CoverFaultDto> getCoverFaultList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = "SELECT cf.CoverFaultPhoto,cf.CoverId,cf.CoverFaultType,cf.CoverFaultReason,cf.CoverFaultId,cf.CoverFaultLevel,cf.CoverFaultTime,cf.CoverCheck,cf.CoverMainCheck\n" +
                "FROM tb_cover c,tb_cover_fault cf\n" +
                "WHERE c.CoverId=cf.CoverId\n";
        //-1:未通过；0：待审核；1：已通过
        if (faultStatus == -1) {
            sql += " AND (cf.CoverCheck=-1 OR cf.CoverMainCheck=-1) ";
        } else if (faultStatus == 0) {
            sql += " AND ((cf.CoverCheck=1 AND cf.CoverMainCheck=0) OR cf.CoverCheck = 0) ";
        } else if (faultStatus == 1) {
            sql += " AND cf.CoverMainCheck = 1 ";
        }
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += " AND cf.CoverOrgId = " + orgId + " ";
        }
        sql += "ORDER BY cf.CoverFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(CoverFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }
}
